/**
 * 
 */
package com.orange.modules.dao.expert;

import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.modules.entity.expert.AuthExpertItemData;
import com.orange.modules.entity.expert.AuthExpertItemMap;

/**
 * @author gaogs
 *
 * 2016-12-14
 */
@Repository
public class AuthExpertItemDataRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthExpertItemDataDao authItemExpertDataDao;

	/**
	 * 根据一级指标查询二级指标或细则等级
	 */
	public List<AuthExpertItemData> queryAuthValueByItemId(String itemId) throws SQLException{
		String sql = "SELECT a.p_id as pId, a.s_item_id as itemId, a.s_index_id as indexId, a.s_value as value, a.s_main_problem as mainProblem, "+ 
				" a.s_school_id as schoolId, a.s_group_id as groupId, a.s_expert_id as expertId, a.s_type as type"+ 
				" FROM t_auth_item_expert_data as a WHERE a.s_item_id = ?";
		List<AuthExpertItemData> list = dbHelper.getBeanList(sql, AuthExpertItemData.class, itemId);
		return list;
	}
	
	/**
	 * 根据专家id和机构id查询分项id
	 */
	public AuthExpertItemData queryAuthItemsByExpertIdAndSchoolId(String expertId,String schoolId) throws SQLException{
		String sql = "SELECT a.p_id as pid,a.s_item_id as itemId,a.s_school_id as schoolId,a.s_expert_id as expertId,"+
				 " a.s_group_id as groupId"+
				 " FROM t_auth_item_expert_map a where a.s_expert_id = ? AND a.s_school_id = ?";
		AuthExpertItemData at = dbHelper.getBean(sql, AuthExpertItemData.class, expertId,schoolId);
		return at;
	}
	
	/**
	 * 保存材料评审分项意见记录表
	 */
	public void saveAuthItemExpertData(AuthExpertItemData ad) throws SQLException{
		authItemExpertDataDao.save(ad);
	}
	
	/**
	 * 根据专家id和机构id和一级指标查询详细信息
	 */
	public List<AuthExpertItemData> queryAuthItemInfo(String schoolId,String expertId,String itemId) throws SQLException{
		String sql = "SELECT a.p_id as pId, a.s_item_id as itemId, a.s_index_id as indexId, a.s_value as value, a.s_main_problem as mainProblem, "+ 
				" a.s_school_id as schoolId, a.s_group_id as groupId, a.s_expert_id as expertId, a.s_type as type"+
				" FROM t_auth_item_expert_data a WHERE a.s_school_id = ? AND a.s_expert_id = ? AND a.s_item_id =?";
		List<AuthExpertItemData> list = dbHelper.getBeanList(sql, AuthExpertItemData.class, schoolId, expertId, itemId);
		return list;
		
	}
	
	/**
	 * 删除数据
	 */
	public void deleteAuthExpertItemData(String expertId, String type, String itemId, String schoolId) throws SQLException{
		String sql="DELETE FROM t_auth_item_expert_data WHERE s_expert_id=? AND s_type=? and s_index_id = ? and s_school_id = ?";
		dbHelper.execute(sql, expertId, type, itemId,schoolId);
	}
	
	/**
	 * 根据一级指标查询二级指标或细则等级
	 */
	public List<AuthExpertItemData> queryAuthAllItem(String itemId) throws SQLException{
		String sql = "SELECT a.p_id as pId, a.s_item_id as itemId, a.s_index_id as indexId, a.s_value as value, a.s_main_problem as mainProblem, "+ 
				" a.s_school_id as schoolId, a.s_group_id as groupId, a.s_expert_id as expertId, a.s_type as type, a.s_content as content"+ 
				" FROM t_auth_item_expert_data as a LEFT JOIN t_auth_item as b ON a.s_item_id = b.p_id WHERE a.s_item_id = ?";
		List<AuthExpertItemData> list = dbHelper.getBeanList(sql, AuthExpertItemData.class, itemId);
		return list;
		
	}
	
	/**
	 * 材料评审--下载表格统计等级数量
	 * @throws SQLException 
	 */
	public AuthExpertItemData queryNumber(String schoolId, String type, String level) throws SQLException{
		String sql = "select count(*) as number from t_auth_item_expert_data WHERE s_school_id =? and s_type = ? and s_value =?";
		return dbHelper.getBean(sql.toString(), AuthExpertItemData.class, schoolId,type,level);
		
	}
}
